<?php
class PhicdcbsController extends AppController
{

	var $name = 'Phicdcbs';
	var $uses=array('Phicdcb','Tgdongdoanphicb','Thang','Chidoansv','Doanphicb','Namhoc');
	var $helpers=array('ctable','FlashChart');
	
	function index($chidoansv_id=null)
	{
		if($chidoansv_id!=null)
		{
			$this->Phicdcb->recursive = 0;
			$this->set('phicdcbs', $this->paginate(array('chidoansv_id'=>$chidoansv_id)));
			$this->set(compact('chidoansv_id'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Không có Hoạt Động này', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('phicdcb', $this->Phicdcb->read(null, $id));
	}

	function add($chidoan_id=null) {
		if($chidoan_id!=null)
		{
			if (!empty($this->data)) {
				$this->data['Phicdcb']['chidoansv_id']=$chidoan_id;
				$this->data['Phicdcb']['ngaynop']=date('Y-m-d',strtotime($this->data['Phicdcb']['ngaynop']));
				$this->data['Phicdcb']['sotien']= str_replace('.','', $this->data['Phicdcb']['sotien']);
				$this->Phicdcb->create();
			if ($this->Phicdcb->save($this->data)) {
				$this->Session->setFlash(__('Thông tin đã lưu', true),'success');
			$this->redirect(array('action' => 'thuchi',$chidoan_id,$this->Session->read('namhocht_id')));
			} else {
				$this->Session->setFlash(__('Không Thể Lưu. Vui Lòng Thử Lại', true),'error');
			}
		}
		$loaichiphis = $this->Phicdcb->Loaichiphi->find('list',array('fields'=>array('tenloaichiphi')));
		$this->set(compact('loaichiphis', 'chidoan_id'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
	}

	function edit($chidoan_id=null,$id = null)
	{
		if($chidoan_id==null)
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
		else
		{
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Không có Hoạt Động Này', true));
			$this->redirect(array('action' => 'index',$chidoan_id));
		}
		if (!empty($this->data)) {
			$this->data['Phicdcb']['chidoansv_id']=$chidoan_id;
			$this->data['Phicdcb']['ngaynop']=date('Y-m-d',strtotime($this->data['Phicdcb']['ngaynop']));
			$this->data['Phicdcb']['sotien']= str_replace('.','', $this->data['Phicdcb']['sotien']);
			if ($this->Phicdcb->save($this->data)) {
				$this->Session->setFlash(__('Thông tin đã lưu', true),'success');
				$this->redirect(array('action' => 'thuchi',$chidoan_id,$this->Session->read('namhocht_id')));
			} else {
				$this->Session->setFlash(__('Thông tin không thể lưu', true),'error');
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Phicdcb->read(null, $id);
			$this->data['Phicdcb']['ngaynop'] =date('d-m-Y',strtotime($this->data['Phicdcb']['ngaynop']));
			$this->data['Phicdcb']['sotien'] = number_format($this->data['Phicdcb']['sotien'],0,',','.'); 
		}
		$loaichiphis = $this->Phicdcb->Loaichiphi->find('list',array('fields'=>array('tenloaichiphi')));
		$this->set(compact('loaichiphis', 'chidoancbs','chidoan_id','id'));
		}
	}

	function delete($chidoan_id=null,$id = null) {
		if(!$chidoan_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		if (!$id) {
			$this->Session->setFlash(__('Không có Hoạt Động Này', true));
			$this->redirect(array('action'=>'index',$chidoan_id));
		}
		if ($this->Phicdcb->delete($id)) {
			$this->Session->setFlash(__('Thông tin đã xóa', true),'success');
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Thông tin không thể xóa', true),'error');
		$this->redirect(array('action' => 'index'));
	}
	
	function thuchi($chidoan=null,$namhoc=null)
	{
		if(!$chidoan || !$namhoc)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
			
			/*
			 * thong tin nam hoc va chi doan
			 */
			$thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc),
				'order'=>array('id ASC')));
			if(!empty($thangs))
			{
        		$thang_id = array_keys($thangs);
			}
       		else
       		{
        		$thang_id= array();
       		}
			
			$chidoansv=$this->Chidoansv->find('first',array(
                'conditions'=>array('Chidoansv.id'=>$chidoan),
                'fields'=>array('Chidoansv.id','tenchidoan','Khoa.tenkhoa','khoahoc_id'),
                'recursive'=>0));
			
			$nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
		
			/*
			 * tao data thu doan phi nam hoc
			 */
			$querystr = "select sum(a.dadong * sotien) as doanphi
							from (SELECT Tgdongdoanphicb.thang_id,count(`Tgdongdoanphicb`.`canbo_id`) as dadong, Doanphicb.sotien
								FROM `tgdongdoanphicbs` AS `Tgdongdoanphicb`,`doanphicbs` as `Doanphicb`
								WHERE Tgdongdoanphicb.thang_id = Doanphicb.thang_id
								AND `Tgdongdoanphicb`.`namhoc_id` =".$namhoc."
								AND `Tgdongdoanphicb`.`chidoansv_id` =".$chidoan."
								group by Tgdongdoanphicb.thang_id) a";
			$doanphinht = $this->Phicdcb->query($querystr);
			/*
			 * tao data thu doan phi tong hop
			 */
			$querystr = "select sum(a.dadong * sotien) as doanphi
							from (SELECT Tgdongdoanphicb.thang_id,count(`Tgdongdoanphicb`.`canbo_id`) as dadong, Doanphicb.sotien
								FROM `tgdongdoanphicbs` AS `Tgdongdoanphicb`,`doanphicbs` as `Doanphicb`
								WHERE Tgdongdoanphicb.thang_id = Doanphicb.thang_id
								AND `Tgdongdoanphicb`.`chidoansv_id` =".$chidoan."
								group by Tgdongdoanphicb.thang_id) a";
			$doanphith = $this->Phicdcb->query($querystr);
			
        	/*
        	 * tao ngay bat dau den ngay ket thuc
        	 */
        	if(!empty($thangs))
        	{
	        	$nams = split('-',$nhoc[key($nhoc)]);
	        	$thang_dau  =$thangs[$thang_id[0]];
	        	$thang_cuoi = $thangs[$thang_id[count($thang_id)-1]];
	        	$ngaybd =date('Y-m-d',strtotime($nams[0].'-'.$thang_dau.'-1'));
	        	$ngaykt='';
	        	for($i=31;$i>=26;$i--)
	        	{
	        		if(checkdate($thang_cuoi, $i, $nams[1]))
	        		{
	        			$ngaykt = date('Y-m-d',strtotime($nams[1].'-'. $thang_cuoi.'-'.$i));
	        			break;
	        		}
	        	}
	        	/*
	        	 * phi thu - chi nam hoc
	        	 */
	        	$phicdsvs = $this->Phicdcb->find('all',array(
	        									'fields'=>array('loaichiphi_id','sum(Phicdcb.sotien) as sotien'),
	        									'conditions'=>array('ngaynop >='=>$ngaybd,
	        														'ngaynop <='=>$ngaykt,
	        														'chidoansv_id'=>$chidoan),
	        									'group'=>array('loaichiphi_id')));
	        	
	        	/*
	        	 * phi thu - chi tong hop
	        	 */
	        	$phicdsvths = $this->Phicdcb->find('all',array(
	        									'fields'=>array('loaichiphi_id','sum(Phicdcb.sotien) as sotien'),
	        									'conditions'=>array('chidoansv_id'=>$chidoan),
	        									'group'=>array('loaichiphi_id')));
	        	/*
	        	 * tao data cho table
	        	 */
	        	$cols=array();
	        	$rows=array();
	        	$data=array();
	        	// cols
	        	$cols[0]='Tổng hợp';
	        	$cols[1]='Năm học '.$nhoc[key($nhoc)];	        	
	        	//row
	        	$rows[0]='Tiền Thu Đoàn Phí';
	        	$rows[1]='Tiền Thu Khác';
	        	$rows[2]='Tổng Thu';
	        	$rows[3]='Tổng Chi';
	        	$rows[4]='Số Dư';
	        	
	        	$chi = 0;
	        	$thu = 0;
	        	foreach ($phicdsvs as $phicdsv) 
	        	{
	        		if($phicdsv['Phicdcb']['loaichiphi_id']==3)
	        			$thu=$phicdsv[0]['sotien'];
	        		else
	        			$chi+=$phicdsv[0]['sotien'];	        		
	        	}
	        	//tong hop
	        	$chith = 0;
	        	$thuth = 0;
        		foreach ($phicdsvths as $phicdsvth) 
	        	{
	        		if($phicdsvth['Phicdcb']['loaichiphi_id']==3)
	        			$thuth=$phicdsvth[0]['sotien'];
	        		else
	        			$chith+=$phicdsvth[0]['sotien'];	        		
	        	}
	        	$dpth = $doanphith[0][0]['doanphi'];
	        	$dpht =$doanphinht[0][0]['doanphi'];
	        	$thth = (empty($thuth)?0:$thuth);
	        	$chth =(empty($chith)?0:$chith);
	        	$data[0][0]=number_format($dpth,0,',','.');
	        	$data[0][1]=number_format($dpht,0,',','.');
	        	//tong hop
	        	$data[1][0] = number_format($thth,0,',','.');
	        	$data[2][0] = number_format($dpth + $thth,0,',','.');
	        	$data[3][0] = number_format($chth,0,',','.');
	        	$data[4][0] = number_format($dpth + $thth-$chth,0,',','.');
	        	//nam hoc ht
	        	$data[1][1] = number_format($thu,0,',','.');
	        	$data[2][1] = number_format($dpht +$thu,0,',','.');
	        	$data[3][1] = number_format($chi,0,',','.');
	        	$data[4][1] = number_format($dpht +$thu-$chi,0,',','.');
	        	$this->Phicdcb->recursive = 0;
				$this->set('phicdcbs', $this->paginate(array('ngaynop >='=>$ngaybd,'ngaynop <='=>$ngaykt,'chidoansv_id'=>$chidoan)));
        	}
        	$this->set(compact('rows','cols','data','chidoan'));
	}

	function bieudo()
	{
		//$chidoan_id = $this->Session->read('chidoanht_id');
		$chidoan_id = 33;
		if(!empty($this->data))
		{
			$namhoc_id = $this->data['Phicdcb']['namhoc'];
		}
		if(!isset($namhoc_id))
		{
			$namhoc_id = $this->Session->read('namhocht_id');			
		}
		$this->Namhoc->displayField='namhoc';
		$namhocs = $this->Namhoc->find('list');
		
		// data
		$thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc_id),
				'order'=>array('id ASC')));
		if(!empty($thangs))
		{
        	$thang_id = array_keys($thangs);
		}
       	else
       	{
        	$thang_id= array();
       	}
       	if(!empty($thangs))
        {
        		$namhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc_id)));
	        	$nams = split('-',$namhoc[$namhoc_id]);
	        	$thang_dau  =$thangs[$thang_id[0]];
	        	$thang_cuoi = $thangs[$thang_id[count($thang_id)-1]];
	        	$ngaybd =date('Y-m-d',strtotime($nams[0].'-'.$thang_dau.'-1'));
	        	$ngaykt='';
	        	for($i=31;$i>=26;$i--)
	        	{
	        		if(checkdate($thang_cuoi, $i, $nams[1]))
	        		{
	        			$ngaykt = date('Y-m-d',strtotime($nams[1].'-'. $thang_cuoi.'-'.$i));
	        			break;
	        		}
	        	}
	        	//doan phi
	        	$querystr = "SELECT Tgdongdoanphicb.thang_id,count(`Tgdongdoanphicb`.`canbo_id`) as dadong, Doanphicb.sotien
								FROM `tgdongdoanphicbs` AS `Tgdongdoanphicb`,`doanphicbs` as `Doanphicb`
								WHERE Tgdongdoanphicb.thang_id = Doanphicb.thang_id
								AND `Tgdongdoanphicb`.`namhoc_id` =".$namhoc_id."
								AND `Tgdongdoanphicb`.`chidoansv_id` =".$chidoan_id."
								group by Tgdongdoanphicb.thang_id";
			$doanphinht = $this->Phicdcb->query($querystr);
	        	/*
	        	 * phi thu - chi nam hoc
	        	 */
	        	$phithus = $this->Phicdcb->find('all',array(
	        									'fields'=>array('DATE_FORMAT(ngaynop,"%m")as thang','sum(Phicdcb.sotien) as sotien'),
	        									'conditions'=>array('ngaynop >='=>$ngaybd,
	        														'ngaynop <='=>$ngaykt,
	        														'chidoansv_id'=>$chidoan_id,'loaichiphi_id'=>3),
	        									'group'=>array('month(ngaynop)','loaichiphi_id')));
	        	$phichis = $this->Phicdcb->find('all',array(
	        									'fields'=>array('DATE_FORMAT(ngaynop,"%m")as thang','sum(Phicdcb.sotien) as sotien'),
	        									'conditions'=>array('ngaynop >='=>$ngaybd,
	        														'ngaynop <='=>$ngaykt,
	        														'chidoansv_id'=>$chidoan_id,'loaichiphi_id !='=>3),
	        									'group'=>array('month(ngaynop)','loaichiphi_id')));
	        	
				$str = 'select max(a.sotien) as MAX
						FROM (SELECT sum(`Phicdcb`.`sotien`) as sotien 
	        			FROM `phicdcbs` AS `Phicdcb` LEFT JOIN `loaichiphis` AS `Loaichiphi` ON (`Phicdcb`.`loaichiphi_id` = `Loaichiphi`.`id`) LEFT JOIN `chidoansvs` AS `Chidoansv` ON (`Phicdcb`.`chidoansv_id` = `Chidoansv`.`id`)  
	        			WHERE `ngaynop` >="'. $ngaybd .  '" AND `ngaynop` <="'.$ngaykt.'" AND `chidoansv_id` ='.$chidoan_id 
	        			.' GROUP BY month(ngaynop), loaichiphi_id) a';
	        	$result = $this->Phicdcb->query($str);
	        	$max = $result[0][0]['MAX'];
	        	$thangs = $this->Thang->find('list',array('conditions'=>array('namhoc_id'=>$namhoc_id),'fields'=>array('thang')));
	        	$result = array();
	        	foreach ($thangs as $key=>$thang)
	        	{
	        		$result[$key]['thang']=$thang;
	        		
	        		foreach ($phichis as $phi)
	        		{
	        			if($thang == $phi[0]['thang'])
	        				$result[$key]['phichi']=$phi[0]['sotien'];
	        		}
	        		foreach ($phithus as $phi)
	        		{
	        			if($thang == $phi[0]['thang'])
	        				$result[$key]['phithu']=$phi[0]['sotien'];
	        		}
	        		
	        		if(!isset($result[$key]['phithu']))
	        			$result[$key]['phithu']=0;
	        		if(!isset($result[$key]['phichi']))
	        			$result[$key]['phichi']=0;
	        			
	        		foreach ($doanphinht as $doanphi)
	        		{
	        			if($doanphi['Tgdongdoanphicb']['thang_id']==$key)
	        			{
	        				$result[$key]['phithu']+=$doanphi[0]['dadong']*$doanphi['Doanphicb']['sotien'];
	        				if($max<$result[$key]['phithu'])
	        					$max = $result[$key]['phithu'];
	        			}
	        			
	        		}
	        	}
        }
        if($max==0)
	        $max=10;
		$this->set(compact('namhocs','namhoc_id','max','result'));
	}

}
